package by.bsu.touragency.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.log4j.Logger;
import by.bsu.touragency.db.beans.City;
import by.bsu.touragency.db.beans.Eat;
import by.bsu.touragency.db.beans.Hotel;
import by.bsu.touragency.db.beans.People;
import by.bsu.touragency.db.beans.Star;
import by.bsu.touragency.db.connection.DBConnector;

public class HotelDAO extends AbstractDAO {

	public static final String SQL_HOTEL_CREATE = " INSERT INTO tourAgencyDB.hotel (name, starId, about, cityId, eatId, peopleId) VALUES (?, ?, ?, ?, ?, ?);";
	public static final String SQL_HOTEL_CREATE_GET_ID = "SELECT * FROM tourAgencyDB.hotel WHERE name = ? AND cityId = ? AND starId= ?;";
	public static final String SQL_HOTEL_IS_EXIST = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.hotel WHERE name = ? AND cityId = ? AND starId= ? ;";
	public static final String SQL_HOTEL_READ_BY_ID = "SELECT * FROM tourAgencyDB.hotel WHERE (id = ?);";
	public static final String SQL_HOTEL_READ_BY_NAME_CITY_STAR = "SELECT * FROM tourAgencyDB.hotel WHERE (name = ? AND cityId = ? AND starId= ?);";
	public static final String SQL_HOTEL_UPDATE = "UPDATE tourAgencyDB.hotel  SET name = ?, about =?,peopleId=?,eatId=? WHERE (id = ?);";
	public static final String SQL_HOTEL_DELETE = "DELETE FROM tourAgencyDB.hotel WHERE id = ?";
	public static final String SQL_HOTEL_IS_USED = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.hotel WHERE hotelId = ? ;";

	private static Logger logger = Logger.getLogger("log");

	private boolean isUsed(int id) {
		logger.info("Is this hotel in use");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_HOTEL_IS_USED);
			int n;
			try {// 3
				statement.setLong(1, id);
				resultSet = statement.executeQuery();
				n = -2;
				resultSet.next();
				n = resultSet.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n > 0) {
				logger.debug("Hotel is used");
				return true;
			} else {
				logger.debug("Hotel is NOT used");
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		}
	}

	private boolean isNExist(Hotel hotel) {
		logger.info("Is this hotel exist");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_HOTEL_IS_EXIST);
			int n;
			try {// 3
				statement.setString(1, hotel.getName());
				statement.setInt(2, hotel.getCity().getId());
				statement.setInt(3, hotel.getStar().getId());
				resultSet = statement.executeQuery();
				n = -2;
				resultSet.next();
				n = resultSet.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n < 1) {
				logger.debug("There is no hotel with this name,type&city");
				return true;
			} else {
				logger.debug("Hotel with this name,type&city already exist");
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		}
	}

	public Hotel create(Hotel hotel) {
		logger.info("Create hotel");
		try {
			if (isNExist(hotel)) {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_HOTEL_CREATE);
				statement.setString(1, hotel.getName());
				statement.setInt(2, hotel.getStar().getId());
				statement.setString(3, hotel.getAbout());
				statement.setInt(4, hotel.getCity().getId());
				statement.setInt(5, hotel.getEat().getId());
				statement.setInt(6, hotel.getPeople().getId());
				statement.executeUpdate();
			}
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_HOTEL_CREATE_GET_ID);
			statement.setString(1, hotel.getName());
			statement.setInt(2, hotel.getCity().getId());
			statement.setInt(3, hotel.getStar().getId());
			resultSet = statement.executeQuery();

			if (resultSet.next()) {
				hotel.setId(resultSet.getInt("id"));
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return hotel;
	}

	// R
	public Hotel readById(int id) {
		logger.info("Read hotel by id");
		Hotel hotel = new Hotel();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_HOTEL_READ_BY_ID);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				hotel.setId(resultSet.getInt("id"));
				hotel.setAbout(resultSet.getString("about"));
				hotel.setName(resultSet.getString("name"));
				int n = resultSet.getInt("starId");
				StarDAO sd = new StarDAO();
				Star star = new Star();
				star = sd.readById(n);
				hotel.setStar(star);

				n = resultSet.getInt("cityId");
				CityDAO cd = new CityDAO();
				City city = new City();
				city = cd.readById(n);
				hotel.setCity(city);

				n = resultSet.getInt("eatId");
				EatDAO ed = new EatDAO();
				Eat eat = new Eat();
				eat = ed.readById(n);
				hotel.setEat(eat);

				n = resultSet.getInt("peopleId");
				PeopleDAO pd = new PeopleDAO();
				People people = new People();
				people = pd.readById(n);
				hotel.setPeople(people);
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return hotel;
	}

	public Hotel readByNameCityStar(Hotel hotel) {
		logger.info("Read hotel by name and type");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_HOTEL_READ_BY_NAME_CITY_STAR);
			statement.setString(1, hotel.getName());
			statement.setInt(2, hotel.getCity().getId());
			statement.setInt(3, hotel.getStar().getId());
			resultSet = statement.executeQuery();
			boolean read = false;
			while (resultSet.next()) {
				read = true;

				hotel.setId(resultSet.getInt("id"));

				int n = resultSet.getInt("starId");
				StarDAO sd = new StarDAO();
				Star star = new Star();
				star = sd.readById(n);
				hotel.setStar(star);

				n = resultSet.getInt("cityId");
				CityDAO cd = new CityDAO();
				City city = new City();
				city = cd.readById(n);
				hotel.setCity(city);

				n = resultSet.getInt("eatId");
				EatDAO ed = new EatDAO();
				Eat eat = new Eat();
				eat = ed.readById(n);
				hotel.setEat(eat);

				n = resultSet.getInt("peopleId");
				PeopleDAO pd = new PeopleDAO();
				People people = new People();
				people = pd.readById(n);
				hotel.setPeople(people);
			}
			if (!read) {
				hotel = create(hotel);
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return hotel;
	}

	// U
	public void update(Hotel hotel) {

		logger.info("Update hotel info");
		try {
			/* if hotel already exist By name city star */
			if (!isNExist(hotel)) {

				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_HOTEL_UPDATE);
				statement.setString(1, hotel.getName());
				statement.setString(2, hotel.getAbout());
				statement.setInt(3, hotel.getPeople().getId());
				statement.setInt(4, hotel.getEat().getId());
				/* if all hotel fiels already set and we shouldn't chandge them */
				if (hotel.getId() == 0) {
					HotelDAO hd = new HotelDAO();
					Hotel h = new Hotel();
					h = hd.readByNameCityStar(hotel);
					hotel.setId(h.getId());// only id
				}
				statement.setInt(5, hotel.getId());
				statement.executeUpdate();

				if (statement != null) {
					statement.close();
				}
			}/* if there is no such hotel */
			else
				hotel = create(hotel);
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	// D
	public void deleteById(int id) {
		logger.info("Delete hotel by id");
		if (!isUsed(id)) {
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_HOTEL_DELETE);
				statement.setInt(1, id);
				statement.executeUpdate();
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
			} catch (Exception e) {
				logger.error("Undefined exeption");
			} finally {
				logger.debug("Close connection");
				close();
			}
		}
	}
}
